Grokking Database Fundamentals for Tech Interviews
Rate this course
Ask Author
Contribute
Back to course home

0% completed

Vote For New Content
Introduction to Database Systems
Course Overview
Overview of Databases
Core Components of Database
Database Architectures
Relational Vs. Non-relational Databases
Data Storage in DBMS
Data Storage Fundamentals
File Organization in DBMS
Data Compression and Encoding
Quiz
Indexing in DBMS
Introduction to Database Indexes
Single Level Indexing
Tree-Based Indexing Techniques
Hash-Based Indexing
Bloom Filters
Quiz
Transaction Management
Understanding the Transaction
Atomicity
Consistency
Isolation
Durability
Quiz
Concurrency Control
Understanding the Concurrency Control
Optimistic vs. Pessimistic Concurrency Control
Lock-Based Concurrency Control
Timestamp-Based Concurrency Control
Multiversion Concurrency Control (MVCC)
Quiz
Distributed Databases, Data Partitioning & Sharding
Fundamentals of Distributed Databases
Partitioning in Databases
Partitioning Types
Sharding in Databases
Distributed Query Processing
Quiz
Replication in Databases
Replication Topologies
Understanding the Consistency Models in Databases
The CAP Theorem and Its Implications
Quiz
Understanding the Consensus
Consensus algorithms
Leader Election Strategies
Quiz
Understanding the Fault Tolerance
Logging Mechanisms in Databases
Checkpointing and Recovery Basics
Coordinated Recovery in Distributed Systems
High Availability Through Replication and Failover
Ensuring Data Resilience: Redundancy, Mirroring, and Recovery
Quiz
Introduction to NoSQL
Key-Value Stores
Document-Oriented Databases
Column-Family and Wide-Column Stores
Graph Databases
Quiz
Quora System Design & Database Design
TikTok System Design & Database Design
Airbnb System Design & Database Design
Snapchat System Design & Database Design
Partitioning in Databases

What is Partitioning in Databases?

Image

Partitioning is the process of dividing a single database table or data set into smaller subsets, each stored and managed independently. While users interact with the database as a whole, the underlying system accesses only the relevant partition(s), significantly improving query performance.

  • Key Goals of Partitioning:
    • Enhance query performance by reducing the amount of data scanned.
    • Improve manageability by isolating data into smaller, logical subsets.
    • Achieve scalability by distributing partitions across multiple nodes in a distributed database.
    • Support fault tolerance by replicating partitions to ensure availability during node failures.

Types of Partitioning

Partitioning is typically categorized into two main types: Horizontal Partitioning and Vertical Partitioning.

Horizontal Partitioning

Horizontal partitioning involves splitting rows of a table into smaller partitions based on a specific criterion. Each partition contains a subset of rows, but all partitions have the same columns.

  • Use Case: Ideal for scenarios where data can be divided logically based on a range or category. For example, customer data can be partitioned by geographic region or transaction dates.

Example

Let’s say we have a table Orders with the following schema:

OrderIDCustomerIDOrderDateAmount
11012023-01-05$50
21022023-02-12$80
31032023-01-18$100
41042023-03-07$40

We can horizontally partition this table based on OrderDate:

  • Partition 1 (Orders from January 2023):
OrderIDCustomerIDOrderDateAmount
11012023-01-05$50
31032023-01-18$100
  • Partition 2 (Orders from February 2023 and later):
OrderIDCustomerIDOrderDateAmount
21022023-02-12$80
41042023-03-07$40

Advantages of Horizontal Partitioning

  • Improves query performance by scanning only the relevant partition.
  • Enables distributed storage, as partitions can be stored on different nodes.

Disadvantages

  • Requires additional logic to manage and query partitions.

Vertical Partitioning

Vertical partitioning splits a table into smaller tables based on columns rather than rows. Each partition contains a subset of the columns, typically grouping them based on their usage patterns.

  • Use Case: Suitable for scenarios where different applications or queries access specific columns frequently. It minimizes the amount of data read during a query, improving performance.

Example

Consider the same Orders table:

OrderIDCustomerIDOrderDateAmount
11012023-01-05$50
21022023-02-12$80
31032023-01-18$100
41042023-03-07$40

We can vertically partition this table as follows:

  • Partition 1 (Order Details):
OrderIDOrderDateAmount
12023-01-05$50
22023-02-12$80
32023-01-18$100
42023-03-07$40
  • Partition 2 (Customer Details):
OrderIDCustomerID
1101
2102
3103
4104

Advantages of Vertical Partitioning

  • Optimizes queries that require only specific columns.
  • Reduces I/O overhead by reading only relevant columns.

Disadvantages

  • May require joins to access data spanning multiple partitions.

When to Use Partitioning

Partitioning is particularly useful in the following scenarios:

  1. Large Tables: Tables with millions or billions of rows can be split to improve query performance.
  2. Frequent Queries on Specific Subsets: If queries frequently access specific ranges of data (e.g., recent transactions), partitioning reduces the amount of scanned data.
  3. Distributed Systems: Partitioning enables horizontal scaling by distributing data across multiple nodes.

Comparison of Horizontal and Vertical Partitioning

AspectHorizontal PartitioningVertical Partitioning
Division CriteriaBased on rows (e.g., date ranges or regions).Based on columns (e.g., frequently used columns).
StoragePartitions stored as subsets of rows.Partitions stored as subsets of columns.
Query OptimizationReduces row scanning for specific data ranges.Reduces column retrieval for specific queries.
Use CaseDistributed databases with large datasets.Databases with column-specific access patterns.

Partitioning is a powerful technique to improve the scalability, performance, and manageability of databases. By splitting data into smaller, focused subsets, both horizontal and vertical partitioning reduce the load on individual nodes and optimize query performance. Horizontal partitioning is ideal for distributing data across nodes, while vertical partitioning is better suited for optimizing column-based queries.

In the next lesson, we will explore specific Partitioning Strategies in Databases to understand how partitioning can be implemented effectively in distributed systems.

Fundamentals of Distributed Databases
Partitioning Types
Mark as Completed

On this page

What is Partitioning in Databases?

Types of Partitioning

Horizontal Partitioning

Vertical Partitioning

When to Use Partitioning

Comparison of Horizontal and Vertical Partitioning